package com.pagepub.expo.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.stereotype.Repository;

import com.pagepub.expo.entity.AppUser;

@Repository
public class AppUserDao {
	@Autowired
	@Qualifier("jdbcTemplate")
	private JdbcTemplate template;
	
	@Autowired
	@Qualifier("appSeqDao")
	private AppSeqDao appSeqDao;
	
	
	private static String tableName="App_USER";
	

	private static String QUERY_ALL = "SELECT userId,userName, password, email, depId,"+
			"position, phone, mobile, fax, address,"+
			" zip, photo, accessionTime,status, education," +
			" fullName, title  FROM "+tableName;

	private static String QUERY_ALL_COUNT = "SELECT COUNT(*) FROM "+  tableName;
	
	private static String INSERT_SQL = "INSERT  INTO "+tableName+" (" +
			"userId,userName, password, email, depId," +
			"position, phone, mobile, fax, address," +
			"zip, photo, accessionTime,status, education, " +
			"fullName, title)VALUES(" +
			":userId,:userName, :password, :email, :depId," +
			":position,:phone, :mobile, :fax, :address," +
			" :zip, :photo, :accessionTime,:status, :education, :fullName, :title)";

	private static String DELETE_SQL = "DELETE FROM "+ tableName +" WHERE userid=?";

	private static String UPDATE_SQL = "UPDATE "+ tableName+" SET " +
			"userName=:userName, password=:password" +
			" WHERE userId=:userId";

	private final ParameterizedRowMapper<AppUser> mapper = new ParameterizedRowMapper<AppUser>() {
		public AppUser mapRow(ResultSet rs, int rowNum) throws SQLException {
			AppUser user = new AppUser();
			user.setUserId(rs.getLong("userId"));
			user.setUserName(rs.getString("userName"));
			user.setPassword(rs.getString("password"));
			user.setEmail(rs.getString("email"));
			user.setDepId(rs.getLong("depId"));
			user.setPosition(rs.getString("position"));
			user.setPhone(rs.getString("phone"));
			user.setMobile(rs.getString("mobile"));
			user.setFax(rs.getString("fax"));
			user.setAddress(rs.getString("address"));
			user.setZip(rs.getString("zip"));
			user.setPhoto(rs.getString("photo"));
			user.setAccessionTime(rs.getDate("accessionTime"));
			user.setStatus(rs.getShort("status"));
			user.setEducation(rs.getString("education"));
			user.setFullName(rs.getString("fullName"));
			user.setTitle(rs.getShort("title"));
			
			return user;
		}
	};

	public List<AppUser> findAll() {
		return template.query(QUERY_ALL, mapper);
	}

	public AppUser findById(Long id) {
		List<AppUser> appUsers = template.query(QUERY_ALL + " WHERE userId=?",
				new Object[] { id }, mapper);
		if (appUsers.size() == 0) {
			return null;
		} else
			return appUsers.get(0);
	}
	
	public AppUser findByUserName(String userName) {
		List<AppUser> appUsers = template.query(QUERY_ALL + " WHERE userName=?",
				new Object[] { userName }, mapper);
		if (appUsers.size() == 0) {
			return null;
		} else
			return appUsers.get(0);
	}

	
	public void insert(AppUser bean) {
		if(bean.getUserId()==null){
			bean.setUserId(new Long(appSeqDao.getSequence("app_user_seq")));
		}		
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(INSERT_SQL, ps);

	}

	public void delete(Long id) {
		template.update(DELETE_SQL, id);
	}

	public void update(AppUser bean) {
		SqlParameterSource ps = new BeanPropertySqlParameterSource(bean);
		NamedParameterJdbcTemplate npTemplate = new NamedParameterJdbcTemplate(
				template.getDataSource());
		npTemplate.update(UPDATE_SQL, ps);
	}
	
	public int getCount(Map<String, String> param){
		int count = template.queryForInt(QUERY_ALL_COUNT);
		return count;
	}
	
	public List findByPage(int currentPage,int pageSize,Map<String, String> param){
		List lists = template.query(QUERY_ALL + " limit ?,?",
				new Object[] { currentPage, pageSize}, mapper);
		return lists;
		
	}
	
	
	

}
